<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
               "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
lang="en" xml:lang="en">
<head>
<title>Water mysql 数据库设计</title>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
<meta name="generator" content="Org-mode"/>
<meta name="generated" content="2012-08-10 15:21:05 CST"/>
<meta name="author" content="wh"/>
<meta name="description" content=""/>
<meta name="keywords" content=""/>
<style type="text/css">
 <!--/*--><![CDATA[/*><!--*/
  html { font-family: Times, serif; font-size: 12pt; }
  .title  { text-align: center; }
  .todo   { color: red; }
  .done   { color: green; }
  .tag    { background-color: #add8e6; font-weight:normal }
  .target { }
  .timestamp { color: #bebebe; }
  .timestamp-kwd { color: #5f9ea0; }
  .right  {margin-left:auto; margin-right:0px;  text-align:right;}
  .left   {margin-left:0px;  margin-right:auto; text-align:left;}
  .center {margin-left:auto; margin-right:auto; text-align:center;}
  p.verse { margin-left: 3% }
  pre {
	border: 1pt solid #AEBDCC;
	background-color: #F3F5F7;
	padding: 5pt;
	font-family: courier, monospace;
        font-size: 90%;
        overflow:auto;
  }
  table { border-collapse: collapse; }
  td, th { vertical-align: top;  }
  th.right  { text-align:center;  }
  th.left   { text-align:center;   }
  th.center { text-align:center; }
  td.right  { text-align:right;  }
  td.left   { text-align:left;   }
  td.center { text-align:center; }
  dt { font-weight: bold; }
  div.figure { padding: 0.5em; }
  div.figure p { text-align: center; }
  textarea { overflow-x: auto; }
  .linenr { font-size:smaller }
  .code-highlighted {background-color:#ffff00;}
  .org-info-js_info-navigation { border-style:none; }
  #org-info-js_console-label { font-size:10px; font-weight:bold;
                               white-space:nowrap; }
  .org-info-js_search-highlight {background-color:#ffff00; color:#000000;
                                 font-weight:bold; }
  /*]]>*/-->
</style>
<script type="text/javascript">
<!--/*--><![CDATA[/*><!--*/
 function CodeHighlightOn(elem, id)
 {
   var target = document.getElementById(id);
   if(null != target) {
     elem.cacheClassElem = elem.className;
     elem.cacheClassTarget = target.className;
     target.className = "code-highlighted";
     elem.className   = "code-highlighted";
   }
 }
 function CodeHighlightOff(elem, id)
 {
   var target = document.getElementById(id);
   if(elem.cacheClassElem)
     elem.className = elem.cacheClassElem;
   if(elem.cacheClassTarget)
     target.className = elem.cacheClassTarget;
 }
/*]]>*///-->
</script>

</head>
<body>
<div id="content">

<h1 class="title">Water mysql 数据库设计</h1>
<p>author:cold night
date:2012-08-06
</p>
<div id="table-of-contents">
<h2>Table of Contents</h2>
<div id="text-table-of-contents">
<ul>
<li><a href="#sec-1">1 MySQL数据库设计 </a></li>
</ul>
</div>
</div>

<div id="outline-container-1" class="outline-2">
<h2 id="sec-1"><span class="section-number-2">1</span> MySQL数据库设计 </h2>
<div class="outline-text-2" id="text-1">

<pre class="example">
为保证和Mongodb兼容,所有表数据加一个字段_id
</pre>


<table border="2" cellspacing="0" cellpadding="6" rules="groups" frame="hsides">
<caption>文章表 posts</caption>
<colgroup><col class="left" /><col class="left" /><col class="left" /><col class="left" /><col class="left" />
</colgroup>
<thead>
<tr><th scope="col" class="left">字段名</th><th scope="col" class="left">字段类型</th><th scope="col" class="left">描述</th><th scope="col" class="left">允许为空</th><th scope="col" class="left">默认值</th></tr>
</thead>
<tbody>
<tr><td class="left"><sub>id</sub></td><td class="left">INT auto_increment</td><td class="left"></td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">author</td><td class="left">INT</td><td class="left">作者id</td><td class="left">N</td><td class="left">0</td></tr>
<tr><td class="left">title</td><td class="left">VARCHAR(255)</td><td class="left">文章标题</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">content</td><td class="left">LONGTEXT</td><td class="left">文章内容</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">views</td><td class="left">INT</td><td class="left">查看数</td><td class="left">N</td><td class="left">0</td></tr>
<tr><td class="left">date</td><td class="left">TIMESTAMP</td><td class="left">发表日期</td><td class="left">Y</td><td class="left">NOW</td></tr>
<tr><td class="left">status</td><td class="left">ENUM(1,2,3)<sup><a class="footref" name="fnr.1" href="#fn.1">1</a></sup></td><td class="left">保留字段(文章状态)</td><td class="left">Y</td><td class="left">1</td></tr>
<tr><td class="left">lastdate</td><td class="left">TIMESTAMP</td><td class="left">保留字段(文章状态)</td><td class="left">Y</td><td class="left">NOW</td></tr>
</tbody>
</table>



<hr/>
<table border="2" cellspacing="0" cellpadding="6" rules="groups" frame="hsides">
<caption></caption>
<colgroup><col class="left" /><col class="left" /><col class="left" /><col class="left" /><col class="left" />
</colgroup>
<thead>
<tr><th scope="col" class="left">字段名</th><th scope="col" class="left">字段类型</th><th scope="col" class="left">描述</th><th scope="col" class="left">允许为空</th><th scope="col" class="left">默认值</th></tr>
</thead>
<tbody>
<tr><td class="left"><sub>id</sub></td><td class="left">INT auto_increment</td><td class="left"></td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">artid</td><td class="left">INT</td><td class="left">文章id</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">key</td><td class="left">VARCHAR(255)</td><td class="left"></td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">value</td><td class="left">VARCHAR(255)</td><td class="left"></td><td class="left">Y</td><td class="left"></td></tr>
</tbody>
</table>


<table border="2" cellspacing="0" cellpadding="6" rules="groups" frame="hsides">
<caption>分类表 categories</caption>
<colgroup><col class="left" /><col class="left" /><col class="left" /><col class="left" /><col class="left" />
</colgroup>
<thead>
<tr><th scope="col" class="left">字段名</th><th scope="col" class="left">字段类型</th><th scope="col" class="left">描述</th><th scope="col" class="left">允许为空</th><th scope="col" class="left">默认值</th></tr>
</thead>
<tbody>
<tr><td class="left"><sub>id</sub></td><td class="left">INT auto_increment</td><td class="left"></td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">name</td><td class="left">VARCHAR(100)</td><td class="left">分类名称</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">parentid</td><td class="left">INT</td><td class="left">父类id(保留字段)</td><td class="left">N</td><td class="left">0</td></tr>
<tr><td class="left">date</td><td class="left">TIMESTAMP</td><td class="left">添加日期</td><td class="left">N</td><td class="left"></td></tr>
</tbody>
</table>



<table border="2" cellspacing="0" cellpadding="6" rules="groups" frame="hsides">
<caption>标签表 tags</caption>
<colgroup><col class="left" /><col class="left" /><col class="left" /><col class="left" /><col class="left" />
</colgroup>
<thead>
<tr><th scope="col" class="left">字段名</th><th scope="col" class="left">字段类型</th><th scope="col" class="left">描述</th><th scope="col" class="left">允许为空</th><th scope="col" class="left">默认值</th></tr>
</thead>
<tbody>
<tr><td class="left"><sub>id</sub></td><td class="left">INT auto_increment</td><td class="left"></td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">name</td><td class="left">VARCHAR(50)</td><td class="left">标签名</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">date</td><td class="left">TIMESTAMP</td><td class="left">添加日期</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">artid</td><td class="left">INT</td><td class="left">所属文章id</td><td class="left">N</td><td class="left"></td></tr>
</tbody>
</table>



<table border="2" cellspacing="0" cellpadding="6" rules="groups" frame="hsides">
<caption>评论表 comments</caption>
<colgroup><col class="left" /><col class="left" /><col class="left" /><col class="left" /><col class="left" />
</colgroup>
<thead>
<tr><th scope="col" class="left">字段名</th><th scope="col" class="left">字段类型</th><th scope="col" class="left">描述</th><th scope="col" class="left">允许为空</th><th scope="col" class="left">默认值</th></tr>
</thead>
<tbody>
<tr><td class="left">id</td><td class="left">INT auto_increment</td><td class="left"></td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left"><sub>id</sub></td><td class="left">VARCHAR(24)</td><td class="left">兼容mongodb</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">name</td><td class="left">VARCHAR(100)</td><td class="left">姓名</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">email</td><td class="left">VARCHAR(100)</td><td class="left">邮箱</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">website</td><td class="left">VARCHAR(100)</td><td class="left">主页</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">content</td><td class="left">TEXT</td><td class="left">内容</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">artid</td><td class="left">INT      x</td><td class="left">所属文章id</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">date</td><td class="left">TIMESTAMP</td><td class="left">评论日期</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">parentid</td><td class="left">INT</td><td class="left">保留字段(被评论的评论id)</td><td class="left">Y</td><td class="left"></td></tr>
<tr><td class="left">status</td><td class="left">ENUM(1,2,3)<sup><a class="footref" name="fnr.2" href="#fn.2">2</a></sup></td><td class="left">保留字段(评论状态)</td><td class="left">Y</td><td class="left">2</td></tr>
</tbody>
</table>


<table border="2" cellspacing="0" cellpadding="6" rules="groups" frame="hsides">
<caption>配置表 options (保留)</caption>
<colgroup><col class="left" /><col class="left" /><col class="left" /><col class="left" /><col class="left" />
</colgroup>
<thead>
<tr><th scope="col" class="left">字段名</th><th scope="col" class="left">字段类型</th><th scope="col" class="left">描述</th><th scope="col" class="left">允许为空</th><th scope="col" class="left">默认值</th></tr>
</thead>
<tbody>
<tr><td class="left"><sub>id</sub></td><td class="left">INT auto_INCREMENT</td><td class="left"></td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">option</td><td class="left">VARCHAR(100)</td><td class="left">选项</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">value</td><td class="left">VARCHAR(200)</td><td class="left">选项值</td><td class="left">Y</td><td class="left">null</td></tr>
</tbody>
</table>


<table border="2" cellspacing="0" cellpadding="6" rules="groups" frame="hsides">
<caption>用户表 users</caption>
<colgroup><col class="left" /><col class="left" /><col class="left" /><col class="left" /><col class="left" />
</colgroup>
<tbody>
<tr><td class="left">字段名</td><td class="left">字段类型</td><td class="left">描述</td><td class="left">允许为空</td><td class="left">默认值</td></tr>
<tr><td class="left"><sub>id</sub></td><td class="left">INT auto_INCREMENT</td><td class="left"></td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">username</td><td class="left">VARCHAR(100)</td><td class="left">用户名</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">password</td><td class="left">VARCHAR(255)</td><td class="left">密码</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">nickname</td><td class="left">VARCHAR(100)</td><td class="left">昵称</td><td class="left">Y</td><td class="left"></td></tr>
<tr><td class="left">date</td><td class="left">TIMESTAMP</td><td class="left">创建时间</td><td class="left">N</td><td class="left">CURRENT<sub>TIMESTAMP</sub></td></tr>
<tr><td class="left">email</td><td class="left">VARCHAR(100)</td><td class="left">邮箱</td><td class="left">N</td><td class="left"></td></tr>
<tr><td class="left">role</td><td class="left">ENUM('0','1','2')<sup><a class="footref" name="fnr.3" href="#fn.3">3</a></sup></td><td class="left">角色</td><td class="left">N</td><td class="left">'1'</td></tr>
<tr><td class="left">picture</td><td class="left">VARCHAR(100)</td><td class="left">头像url(保留字段)</td><td class="left">Y</td><td class="left">null</td></tr>
<tr><td class="left">lasttime</td><td class="left">TIMESTAMP</td><td class="left">最后登录时间</td><td class="left">Y</td><td class="left"></td></tr>
<tr><td class="left">lastlogin</td><td class="left">VARCHAR(255)</td><td class="left">最后登录ip</td><td class="left">Y</td><td class="left"></td></tr>
</tbody>
</table>




<p>
[fn:3] 如果为0,则是不对应文章id的
</p>

<div id="footnotes">
<h2 class="footnotes">Footnotes: </h2>
<div id="text-footnotes">
<p class="footnote"><sup><a class="footnum" name="fn.1" href="#fnr.1">1</a></sup> 1:发表 2:草稿 3:回收
</p>

<p class="footnote"><sup><a class="footnum" name="fn.2" href="#fnr.2">2</a></sup> 1:发表 2:待通过 3:垃圾
</p>

<p class="footnote"><sup><a class="footnum" name="fn.3" href="#fnr.3">3</a></sup> 0:管理员 1:普通用户 2:保留
</p>
</div>
</div>

</div>
</div>
<div id="postamble">
<p class="date">Date: 2012-08-10 15:21:05 CST</p>
<p class="author">Author: wh</p>
<p class="creator">Org version 7.6 with Emacs version 23</p>
<a href="http://validator.w3.org/check?uri=referer">Validate XHTML 1.0</a>
</div>
</div>
</body>
</html>
